--select EmailID, EmailTo, EscalatedTeamID, CreatedDate from tblEmail 
--Where (EmailTo LIKE '%crimsonlogic.com.sg%' OR EmailTo LIKE '%mahindrasatyam.com%')
--Order by EmailID DESC

--select EmailID, EmailTo, EscalatedTeamID, CreatedDate from tblEmail 
--Where EscalatedTeamID IS NOT NULL
--Order by EmailID DESC

--select top 10 E.* from tblEmail E
--INNER JOIN tblCaseProfileIndex CPI ON E.EmailID = CPI.CaseRefID And CPI.CaseSourceID = 2
--Where (E.EmailTo LIKE '%crimsonlogic.com.sg%' OR E.EmailTo LIKE '%mahindrasatyam.com%')
--Order by E.EmailID DESC

--select top 10 * from tblEmailMessage Where EmailID = 31279

--select top 10 * from tblEmail E Where E.CaseRefID > 1
--And (E.EmailTo LIKE '%crimsonlogic.com.sg%' OR E.EmailTo LIKE '%mahindrasatyam.com%')
--Order by E.EmailID DESC

Declare @StartDate datetime
Declare @EndDate datetime

SET @StartDate = '31-Dec-2013 6:00 PM'
SET @EndDate = '31-Jan-2014 6:00 PM'

--select * from tblCaseProfileIndex
--Where CaseSourceID = 1 And IsReplyCase = 0
--And CreatedDate Between @StartDate And @EndDate

select count(CaseRefID) As TotalIncomingAskACRACount from tblCaseProfileIndex
Where CaseSourceID = 1 And IsReplyCase = 0
And CreatedDate Between @StartDate And @EndDate

select count(CaseRefID) As TotalIncomingEmailCount from tblCaseProfileIndex
Where CaseSourceID = 2 And IsReplyCase = 0
And CreatedDate Between @StartDate And @EndDate

Select Count(CaseProfileIndex) AAC1D3DClosed  From tblCaseProfileIndex 
Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate And NoOfWorkingDays <= 3
And (EscalatedToTeamID IS NULL OR EscalatedToTeamID IN (Select TeamID from tblTeam Where TeamTypeID = 1))

Select Count(CaseProfileIndex) AAC1D3DClosedACRA  From tblCaseProfileIndex 
Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate And NoOfWorkingDays <= 3
And (EscalatedToTeamID IN (Select TeamID from tblTeam Where TeamTypeID = 2))

Select Count(CaseProfileIndex) AATotalActive  From tblCaseProfileIndex 
Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus <> 100 And CreatedDate Between @StartDate And @EndDate 

Select Count(CaseProfileIndex) AAOver3DClosed  From tblCaseProfileIndex 
Where CaseSourceID = 1 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate And NoOfWorkingDays > 3

Select Count(CaseProfileIndex) DEC1D3DClosed From tblCaseProfileIndex 
Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate And NoOfWorkingDays <= 3
And (EscalatedToTeamID IS NULL OR EscalatedToTeamID IN (Select TeamID from tblTeam Where TeamTypeID = 1))

Select Count(CaseProfileIndex) DEC1D3DClosedACRA From tblCaseProfileIndex 
Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate And NoOfWorkingDays <= 3
And (EscalatedToTeamID IN (Select TeamID from tblTeam Where TeamTypeID = 2))

Select Count(CaseProfileIndex) DETotalActive From tblCaseProfileIndex 
Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus <> 100 And CreatedDate Between @StartDate And @EndDate

Select Count(CaseProfileIndex) DEOver3DClosed From tblCaseProfileIndex 
Where CaseSourceID = 2 And IsReplyCase = 0 And CaseStatus = 100 And CreatedDate Between @StartDate And @EndDate And NoOfWorkingDays > 3

Select Count(CPI.CaseProfileIndex) AAC1D3DTask  From tblCaseProfileIndex CPI 
INNER JOIN tblTask T ON CPI.CaseProfileIndex = T.CaseProfileIndex
Where CPI.CaseSourceID = 1 And CPI.IsReplyCase = 0 And CPI.CreatedDate Between @StartDate And @EndDate
And (dbo.funcGetWorkingDays(T.CaseReceivedDate, 
		(select MIN(TH.CreatedDate) from tblTaskHistory TH 
			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
			Where TE.TeamTypeID = 2 And TH.TaskID = T.TaskID)
		)) <= 3


Select Count(CPI.CaseProfileIndex) DEC1D3DTask  From tblCaseProfileIndex CPI 
INNER JOIN tblTask T ON CPI.CaseProfileIndex = T.CaseProfileIndex
Where CPI.CaseSourceID = 2 And CPI.IsReplyCase = 0 And CPI.CreatedDate Between @StartDate And @EndDate
And (dbo.funcGetWorkingDays(T.CaseReceivedDate, 
		(select MIN(TH.CreatedDate) from tblTaskHistory TH 
			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
			Where TE.TeamTypeID = 2 And TH.TaskID = T.TaskID)
		)) <= 3

--select Count(EmailID) from tblEmail 
--Where CreatedDate Between @StartDate And @EndDate 
--And (EmailTo LIKE '%crimsonlogic.com.sg%' OR EmailTo LIKE '%mahindrasatyam.com%')


